package com.sxt.dao;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.ConvertUtils;
import org.junit.Test;

import com.sxt.bean.BBSBoard;

public class DBDao {
	private String driver = "com.mysql.jdbc.Driver";
	private String url = "jdbc:mysql://localhost:3306/sxtbbs";
	private String user = "root";
	private String pass = "liangjie";

	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;

	/**
	 * 获得连接
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public void getConn() throws ClassNotFoundException, SQLException {
		Class.forName(driver);
		conn = DriverManager.getConnection(url, user, pass);
	}

	/**
	 * 通过sql语句和一个实体类，获得实体类集合
	 * 
	 * @param sql
	 * @param clazz
	 * @return
	 */
	public List<?> QueryDb(String sql, Class<?> clazz) {
		List<Object> list = new ArrayList<Object>();
		try {
			getConn();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			Field[] fields = clazz.getDeclaredFields();
			while (rs.next()) {
				Object obj = clazz.newInstance();
				for (Field field : fields) {
					String fieldName = field.getName();
					Method setter = clazz.getMethod("set"
							+ fieldName.substring(0, 1).toUpperCase()
							+ fieldName.substring(1), field.getType());
					setter.invoke(
							obj,
							ConvertUtils.convert(rs.getObject(fieldName),
									field.getType()));
				}
				list.add(obj);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			close();
		}
		return list;
	}

	/**
	 * 关闭连接
	 */
	public void close() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 测试方法
	 */
	@Test
	public void test() {
		/*String sql = "select * from bbscity";
		@SuppressWarnings("unchecked")
		List<City> list = (List<City>) QueryDb(sql, City.class);
		for (City city : list) {
			System.out.println(city);
		}*/
		
		String sql = "select boaname, boadate, boatime from bbsboard";
		@SuppressWarnings("unchecked")
		List<BBSBoard> list = (List<BBSBoard>) QueryDb(sql, BBSBoard.class);
		for (BBSBoard bbsBoard : list) {
			System.out.println(bbsBoard);
		}
	}
}
